/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package com.aptech.dao;

import com.aptech.beans.Suppliers;
import com.aptech.utility.DBType;
import com.aptech.utility.DBUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 *
 * @author Anh Nguyen
 */
public class SupplierManager {

    public static ResultSet displayAllRows() throws SQLException {
        String sql = "SELECT Suppliers.SupplierID, Suppliers.SupplierName, SupplierTypes.SupplierTypeName, "
                + " Suppliers.SupplierAddress, Suppliers.SupplierPhone, Suppliers.SupplierEmail"
                + " FROM Suppliers"
                + " INNER JOIN SupplierTypes"
                + " ON Suppliers.SupplierTypeID = SupplierTypes.SupplierTypeID";
        Connection conn = DBUtil.getConnection(DBType.myConnection);
        PreparedStatement preparedStatement = conn.prepareStatement(sql,
                ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
        ResultSet resultSet = preparedStatement.executeQuery();
        return resultSet;
    }

    public static Suppliers checkName(String SupplierName) throws SQLException {
        String sql = "SELECT * FROM Suppliers WHERE SupplierName = ?";
        Connection conn = DBUtil.getConnection(DBType.myConnection);
        PreparedStatement preparedStatement = conn.prepareStatement(sql,
                ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

        preparedStatement.setString(1, SupplierName);
        ResultSet resultSet = preparedStatement.executeQuery();
        if (resultSet.next()) {
            Suppliers supplier = new Suppliers();
            supplier.setSupplierName(SupplierName);
            return supplier;
        } else {
            return null;
        }
    }

    public static boolean insert(Suppliers supplier) throws SQLException {
        String sql = "INSERT INTO Suppliers (SupplierName, SupplierAddress,"
                + "SupplierPhone, SupplierEmail, SupplierTypeID) VALUES (?, ?, ?, ?, ?)";
        Connection conn = DBUtil.getConnection(DBType.myConnection);
        conn.setAutoCommit(false);

        PreparedStatement preparedStatement = conn.prepareStatement(sql,
                ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
        preparedStatement.setString(1, supplier.getSupplierName());
        preparedStatement.setString(2, supplier.getSupplierAddress());
        preparedStatement.setString(3, supplier.getSupplierPhone());
        preparedStatement.setString(4, supplier.getSupplierEmail());
        preparedStatement.setInt(5, supplier.getSuppliertypeID());

        int affected = preparedStatement.executeUpdate();

        conn.commit(); //transaction block end
        return affected == 1;
    }

    public static boolean update(Suppliers supplier) throws SQLException {
        String sql = "UPDATE Suppliers SET SupplierName=? , SupplierAddress=? , SupplierPhone=? , "
                + "SupplierEmail=? , SupplierTypeID=?"
                + " WHERE SupplierID=?";

        Connection conn = DBUtil.getConnection(DBType.myConnection);
        PreparedStatement preparedStatement = conn.prepareStatement(sql,
                ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

        preparedStatement.setString(1, supplier.getSupplierName());
        preparedStatement.setString(2, supplier.getSupplierAddress());
        preparedStatement.setString(3, supplier.getSupplierPhone());
        preparedStatement.setString(4, supplier.getSupplierEmail());
        preparedStatement.setInt(5, supplier.getSuppliertypeID());
        preparedStatement.setInt(6, supplier.getSupplierID());
        int affected = preparedStatement.executeUpdate();

        conn.commit();
        return affected == 1;

    }

    public static ResultSet getTypeID() throws SQLException {
        String sql = "SELECT * FROM SupplierTypes";
        Connection conn = DBUtil.getConnection(DBType.myConnection);
        PreparedStatement preparedStatement = conn.prepareStatement(sql,
                ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
        ResultSet resultSet = preparedStatement.executeQuery();

        return resultSet;
    }

    public static Suppliers checkTypeIDByTypeName(String TypeName) throws SQLException {
        String sql = "SELECT SupplierTypeID FROM SupplierTypes WHERE SupplierTypeName=?";
        Connection conn = DBUtil.getConnection(DBType.myConnection);
        PreparedStatement preparedStatement = conn.prepareStatement(sql,
                ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

        preparedStatement.setString(1, TypeName);

        ResultSet resultSet = preparedStatement.executeQuery();

        if (resultSet.next()) {
            Suppliers supplierTypeName = new Suppliers();
            supplierTypeName.setSupplierTypeID(resultSet.getInt(1));
            return supplierTypeName;
        } else {
            return null;
        }
    }
}
